USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (
       SELECT *
       FROM   sysobjects
       WHERE  TYPE = 'FN' AND NAME = 'fn_delete_val'
   )
BEGIN
    DROP FUNCTION TSQL.fn_delete_val
    PRINT '<<< DROP FUNCTION tsql.fn_delete_val - Completed with SUCCESS >>>'
END
GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*================================================================================================
Name:			tsql.fn_delete_val
Type:			User defined function 
Owner:					
Description:	Sorts the elements in a list ASCENDIND/DESCENDING   
Parameters:		@list = list of values comma delimited
				@item_no = item_no to delete  
Dependens on:  	fn_split
Usage:			SELECT [tsql].[fn_delete_val] ('7,2,12,23,3,4,5',2)
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_delete_val&referringTitle=Documentation		
Version:		20120905
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		09/05/2012 - Adrian E Dudau - Function created
History:		09/Apr/2013 - Thavarajan M - Function Changed For Efficiency
================================================================================================*/

CREATE FUNCTION [tsql].[fn_delete_val]
(
	@list NVARCHAR (MAX), @item_no BIGINT
)
RETURNS NVARCHAR (MAX)
AS
BEGIN
	/*FUNCTION*/
	DECLARE @Result NVARCHAR (MAX) 
	DECLARE @Findvalue NVARCHAR (MAX)
	SET @Result= ','+@list+','; 
	SET @Findvalue= ','+cast(@item_no AS NVARCHAR(MAX))++','
	SET @Result =  REPLACE(@Result, @Findvalue,',')
	SET @Result = RIGHT(@Result,len(@Result)-1)
	SET @Result = LEFT(@Result,len(@Result)-1)
	RETURN @Result
END /*FUNCTION*/
GO


---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS(
       SELECT *
       FROM   sysobjects
       WHERE  TYPE = 'FN' AND NAME = 'fn_delete_val'
   )
BEGIN
    PRINT '<<< CREATE FUNCTION tsql.fn_delete_val - Completed with SUCCESS >>>'
END
ELSE
BEGIN
    PRINT '<<< CREATE FUNCTION tsql.fn_delete_val - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------


